package com.zretc.emptraining.dbtools;

import java.lang.reflect.InvocationTargetException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.ResultSetMetaData;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.apache.commons.beanutils.BeanUtils;



//封装，数据库工具包
public class DBTools {
	// 私有化变量，静态变量才可以被静态方法调用，常量值不能被修改
	private static final String url = "jdbc:mysql://localhost:3306/emptraining";
	private static final String user = "root";
	private static final String password = "root";

	// 定义连接方法
	public static Connection getConnection() {
		Connection conn = null;
		try {
			// 可能发生异常
			// 将某些类载入到虚拟机
			Class.forName("com.mysql.jdbc.Driver");
			// 得到连接对象
			conn = DriverManager.getConnection(url, user, password);
		} catch (ClassNotFoundException | SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
		return conn;
	}

	// 定义增删改的方法
	public static int getUpdate(String sql, Object... params) {
		// 调用连接的方法
		Connection conn = getConnection();
		// 初始化n的值
		int n = 0;
		PreparedStatement pstmt = null;

		try {
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
//			System.out.println(pstmt);
			// 执行操作
			n = pstmt.executeUpdate();
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				pstmt.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return n;
	}

	// 定义查询的方法
	public static Object exQuery(String sql, Class cls, Object... params) {
		// 创建集合
		List<Object> list = new ArrayList<Object>();
		// 调用连接的方法
		Connection conn = getConnection();
		// 初始化预处理对象的值
		PreparedStatement pstmt = null;
		ResultSet rs = null;
		try {
			// 获得预处理对象
			pstmt = conn.prepareStatement(sql);
			if (params != null) {
				for (int i = 0; i < params.length; i++) {
					pstmt.setObject(i + 1, params[i]);
				}
			}
			// 执行查询操作
			rs = pstmt.executeQuery();

			//判断
			if ("java.lang.Object".equals(cls.getName())) {
				if (rs.next()) {
					return rs.getInt(1);
				}
			}
			// 检索此 ResultSet对象的列的数量，类型和属性,返回ResultSetMetaData
			ResultSetMetaData rsmd = rs.getMetaData();
			// 获取列数
			int count = rsmd.getColumnCount();
			// 遍历
			while (rs.next()) {
				// 根据cls来创建指定类型的对象
				Object bean = cls.newInstance();
				// 遍历每个元素
				for (int i = 1; i <= count; i++) {
					// 获取列名
					String name = rsmd.getColumnName(i);
					// 获取该列的值
					Object value = rs.getObject(i);
					// 利用工具，创建对象
					BeanUtils.copyProperty(bean, name, value);
				}
				// 添加到集合中
				list.add(bean);
			}
		} catch (SQLException | InstantiationException | IllegalAccessException | InvocationTargetException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			// 释放资源
			try {
				rs.close();
			} catch (SQLException e1) {
				// TODO Auto-generated catch block
				e1.printStackTrace();
			}
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		return list;
	}


	/**
	 * 分页的通用方法，mysql数据库
	 * 
	 * @param sql  sql语句
	 * @param cla  XXX.class 类型
	 * @param page    页码，第几页
	 * @param pageSize 每页显示的记录数
	 * @param params 参数
	 * @return
	 */
	public static PageData exQueryByPage(String sql, Class cla, int page, int pageSize, Object... params) {

		String newsql = "select count(1) from (" + sql + ")  as t";
		// 如果传递的是object对象，查询操作得到就是单个结果
		int totalCount = (int) exQuery(newsql, Object.class, params);
		
		// 可以加page的判断
		if (page < 1) {
			page = 1;
		}
		//
		int start = (page - 1) * pageSize;
		sql = sql + " limit " + start + "," + pageSize;

		// 页面展示的数据集
		List data = (List) exQuery(sql, cla, params);

		PageData pageData = new PageData<>(data, page, totalCount, pageSize);

		return pageData;
	}

	/**
	 * 释放资源
	 * 
	 * @param rs    结果集对象
	 * @param pstmt 预处理对象
	 * @param conn  连接对象
	 */
	public static void closeAll(ResultSet rs, PreparedStatement pstmt, Connection conn) {
		if (rs != null) {
			try {
				rs.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (pstmt != null) {
			try {
				pstmt.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
		if (conn != null) {
			try {
				conn.close();
			} catch (SQLException e) {
				// TODO Auto-generated catch block
				e.printStackTrace();
			}
		}
	}
	/**
	 * 事务处理操作,稍难
	 * 
	 * @param tran
	 * @return
	 */
	public static Object transaction(ITransaction tran) {
		Connection conn = getConnection();
		try {
			conn.setAutoCommit(false);
			Object obj = tran.execute(conn);
			conn.commit();
			return obj;
		} catch (Exception e) {
			try {
				conn.rollback();
			} catch (SQLException e1) {
				throw new RuntimeException("回滚失败!", e1);
			}
			throw new RuntimeException("事务执行失败", e);
		} finally {
			closeAll(null, null, conn);
		}
	}

}
